package com.ivr.cm.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.ivr.cm.dao.ILimitCodeDao;
import com.ivr.cm.util.BaseUtil;
import com.ivr.cm.vo.LimitCodeVo;

/**
 * 业务总量配置信息实现
 * @author liugeng
 * @date 2015-12-15
 * @version 1.0
 */
public class LimitCodeDaoImpl extends JdbcDaoSupport implements ILimitCodeDao {
	private static Logger logger = Logger.getLogger(LimitCodeDaoImpl.class);
	
	/**根据当前日期获得业务总量数据列表*/
	@SuppressWarnings("unchecked")
	@Override
	public List<LimitCodeVo> getLimitCodeList() {
		String sql = "select sp_code_id, province, day_limit, month_limit from tb_ivr_limit_code where status = 1";
		logger.info(String.format("根据当前日期获得业务总量数据列表[%s]", sql));
		
		List<LimitCodeVo> limitBusinessVoList = this.getJdbcTemplate().query(sql, rowMapper);
		if(limitBusinessVoList != null && limitBusinessVoList.size() > 0){
			return limitBusinessVoList;
		}
		return null;
	}
	
	private ParameterizedRowMapper<LimitCodeVo> rowMapper = new ParameterizedRowMapper<LimitCodeVo>() {
		@Override
		public LimitCodeVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			LimitCodeVo limitCodeVo = new LimitCodeVo();
			limitCodeVo.setSp_code_id(rs.getInt("sp_code_id"));
			limitCodeVo.setProvince(rs.getString("province"));
			limitCodeVo.setDay_limit(rs.getInt("day_limit"));
			limitCodeVo.setMonth_limit(rs.getInt("month_limit"));
			return limitCodeVo;
		}
	};
	/**根据业务短号码获得对应的业务ID*/
	@SuppressWarnings("unchecked")
	@Override
	public int getSpCodeIdBySpCode(String sp_code) {
		String sql = "select id from tb_ivr_config_business where sp_code = ?";
		Object[] params = new Object[]{sp_code};
		logger.info(String.format("根据业务短号码获得对应的业务ID[%s]", BaseUtil.logSQL(sql, params)));
		
		List<Integer> idList = this.getJdbcTemplate().query(sql, params, tempMapper);
		if(idList != null){
			return idList.get(0);
		}
		return 0;
	}
	private ParameterizedRowMapper<Integer> tempMapper = new ParameterizedRowMapper<Integer>() {
		@Override
		public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
			return rs.getInt("id");
		}
	};
	/**根据业务ID和省份获得短号码在当前省份当日已经拨打金额*/
	@Override
	public int getSpCodeUsedDay(String sp_code, String province, String day_time) {
		String sql = "select floor(ifnull(sum(fee_number),0)/100) fee_number from tb_ivr_user_bill where sp_number like ? and province = ? and date_format(str_to_date(time_end,'%Y-%m-%d'),'%Y-%m-%d') = ? and isfilter = 0";
		Object[] param = new Object[]{sp_code + "%", province, day_time};
		logger.info(String.format("根据渠道ID和省份获得渠道在当前省份当日已经拨打金额[%s]", BaseUtil.logSQL(sql, param)));
		
		int count = this.getJdbcTemplate().queryForInt(sql, param);
		return count;
	}
	/** 根据省份查询所有短号码在当前省份当天的拨打总金额 */
	@Override
	public int getTotalUsedDay(String province, String day_time) {
		String sql = "select floor(ifnull(sum(fee_number),0)/100) fee_number from tb_ivr_user_bill where province = ? and date_format(str_to_date(time_end,'%Y-%m-%d'),'%Y-%m-%d') = ? and isfilter = 0";
		Object[] param = new Object[]{province, day_time};
		logger.info(String.format("根据省份所有短号码在当前省份当日已经拨打金额[%s]", BaseUtil.logSQL(sql, param)));
		
		int count = this.getJdbcTemplate().queryForInt(sql, param);
		return count;
	}
	/**根据业务ID和省份获得短号码在当前省份当月已经拨打金额*/
	@Override
	public int getSpCodeUsedMonth(String sp_code, String province, String month_time) {
		String sql = "select floor(ifnull(sum(fee_number),0)/100) fee_number from tb_ivr_user_bill where sp_number like ? and province = ? and date_format(str_to_date(time_end,'%Y-%m'),'%Y-%m') = ? and isfilter = 0";
		Object[] param = new Object[]{sp_code + "%", province, month_time};
		logger.info(String.format("根据渠道ID和省份获得渠道在当前省份当月已经拨打金额[%s]", BaseUtil.logSQL(sql, param)));
		
		int count = this.getJdbcTemplate().queryForInt(sql, param);
		return count;
	}
}